// Do file that puts together data for the candidacy-placement paper
// Much of the code is borrowed from Henrik Andersson

// Last changed: 2020-06-02(KOL)

  clear all
  cd "D:\SCB_ConPol\Stata"
  set more off
  
  do "C:\Userdata\Shared\Dofiles\DoData\UtplaceringsData\utp-Familydata"
 
  foreach y in 85 86 87 88 89 90 91 92 93 94 {
///OPEN AND PREPARE MIGRATION FILE

use "RTB\migrationer", clear

*Create immigration variable - keep immigrants

  tab PostTyp, gen(m)
  keep if m1==1
  drop PostTyp m1 m2
 
*Generate year, month and day of arrival - keep only year in question 

  tostring Datum, replace
  gen im_year = substr(Datum, 1, 4) 
  destring im_year, replace

  gen im_month = substr(Datum, 5, 2) 
  destring im_month, replace

  gen im_day = substr(Datum, 7, 2) 
  destring im_day, replace

  keep if im_year==19`y'

*small share who immigrated twice during a year

  duplicates tag LopNr, gen(tag)
  sort tag LopNr
  
*Keep the observation from the latest immigration month
  bysort LopNr: egen latestmonth = max(im_month)
  drop if im_month!=latestmonth & tag>0
  drop tag 
  duplicates tag LopNr, gen(tag)
  drop if tag == 1
  drop tag latestmonth

///MERGE WITH INDIVIDUAL DATA (RTB)

*Use 1:m beacause there are some id-number duplicates in RTB-file
  merge 1:m LopNr using "RTB\RTB_19`y'"
  keep if _merge==3
  drop _merge

*Small number of duplicates remaining - (19 ind. in 1990) - throw out
  duplicates tag LopNr, gen(tag)
  drop if tag==1
  drop tag SenPnr /*no variation*/

///MERGE WITH BIRTH COUNTRY DATA
  merge 1:m LopNr using "RTB\FodelselandIFAUGrupp"
  keep if _merge==3
  drop _merge SenPnr /*no variation*/

***Now create fill in the information on country of birth (labels)

  lab def IFAUkod 0 "Sweden"
  lab def IFAUkod 26 "Finland", add
  lab def IFAUkod 27 "Denmark", add
  lab def IFAUkod 28 "Norway & Iceland", add
  lab def IFAUkod 29 "Bosnia", add
  lab def IFAUkod 30 "Yugoslavia", add
  lab def IFAUkod 31 "Poland", add
  lab def IFAUkod 32 "UK & Ireland", add
  lab def IFAUkod 33 "Germany", add
  lab def IFAUkod 34 "Mediterranian", add
  lab def IFAUkod 35 "Baltic", add
  lab def IFAUkod 36 "East Europe", add
  lab def IFAUkod 37 "Hungary & Czech & Slovakia", add
  lab def IFAUkod 38 "Benelux & Swizz & Austria", add
  lab def IFAUkod 39 "US & Canada", add
  lab def IFAUkod 40 "Central America", add
  lab def IFAUkod 41 "Chile", add
  lab def IFAUkod 42 "South America", add
  lab def IFAUkod 43 "East Africa", add
  lab def IFAUkod 44 "Mena", add
  lab def IFAUkod 45 "Other Africa", add
  lab def IFAUkod 46 "Iran", add
  lab def IFAUkod 47 "Iraq", add  
  lab def IFAUkod 48 "Turkey", add
  lab def IFAUkod 49 "East Asia", add
  lab def IFAUkod 50 "South East Asia", add
  lab def IFAUkod 51 "South Asia", add
  lab def IFAUkod 52 "Oceania", add
  lab def IFAUkod 53 "Unknown or Stateless", add

  lab values IFAUkod IFAUkod	

  tab IFAUkod, sort

//	Create a variable that records flyktinginvandring (here I follow the scheme in Table A2 Åslund and Fredriksson")	
	gen FlyktInv=0
	replace FlyktInv=1	if IFAUkod==29
	replace FlyktInv=1	if IFAUkod==30
	replace FlyktInv=1	if IFAUkod==31
	replace FlyktInv=1	if IFAUkod==35
	replace FlyktInv=1	if IFAUkod==36
	replace FlyktInv=1	if IFAUkod==37
	replace FlyktInv=1	if IFAUkod==40
	replace FlyktInv=1	if IFAUkod==41
	replace FlyktInv=1	if IFAUkod==42
	replace FlyktInv=1	if IFAUkod==43
	replace FlyktInv=1	if IFAUkod==44
	replace FlyktInv=1	if IFAUkod==45
	replace FlyktInv=1	if IFAUkod==46
	replace FlyktInv=1	if IFAUkod==47
	replace FlyktInv=1	if IFAUkod==48
	replace FlyktInv=1	if IFAUkod==50
	replace FlyktInv=1	if IFAUkod==51

		
///MERGE ON DEATH DATE 

  merge 1:m LopNr using "RTB\DodDat", keepusing(DodDatum SenPnr)
  drop if _merge==2
  drop _merge SenPnr
  duplicates tag LopNr, gen(tag)
  drop if tag==1

///MERGE ON MORE BIRTH DATA TO GET SEX AND AGE
*1:m again
  merge 1:m LopNr using "RTB\Fodelseuppg", keepusing(Kon UtlSvBakG FodArMan)
  keep if _merge==3
  *duplicates report LopNr - OK
  drop _merge 

*Rename sex-variable and make Men= 1, women= 0.
  rename Kon sex
  replace sex=0 if sex==2
 
///CREATE YEAR AND AGE VARIABLE
  gen year=19`y'

  tostring FodArMan, replace
  gen year_born = substr(FodArMan, 1, 4) 
  destring year_born, replace

  gen month_born = substr(Datum, 5, 2) 
  destring month_born, replace

  gen age =year-year_born

  save  "E:\ProjData\UtplaceringsData8594\temp`y'", replace

}
  
  foreach y in 85 86 87 88 89 90 91 92 93 94 {

  ///OPEN AND PREPARE MIGRATION FILE

 use "RTB\migrationer", clear
  

*Create immigration variable - keep immigrants
  tab PostTyp, gen(m)
  keep if m1==1
  drop PostTyp m1 m2
 
*Generate year, month and day of arrival - keep only year in question 

  tostring Datum, replace
  gen im_year = substr(Datum, 1, 4) 
  destring im_year, replace

  gen im_month = substr(Datum, 5, 2) 
  destring im_month, replace

  gen im_day = substr(Datum, 7, 2) 
  destring im_day, replace

  keep if im_year==19`y'

*small share who immigrated twise during a year

  duplicates tag LopNr, gen(tag)
  sort tag LopNr

*Keep the observation from the latest immigration month
  bysort LopNr: egen latestmonth = max(im_month)
  drop if im_month!=latestmonth & tag>0
  drop tag 
  duplicates tag LopNr, gen(tag)
  drop if tag == 1
  drop tag latestmonth

///MERGE WITH INDIVIDUAL DATA (RTB)

*Use 1:m beacause there are some id-number duplicates in RTB-file
  merge 1:m LopNr using "RTB\RTB_19`y'"
  drop if _merge==1 /*Not left in th country at the end of the year*/
  drop _merge

*Small number of duplicates remaining - throw out one obs.
  duplicates drop LopNr, force

///MERGE WITH BIRTH COUNTRY DATA
  merge 1:m LopNr using "RTB\FodelselandIFAUGrupp"
  drop _merge 
  keep if Kommun!=. /*Keep only residents in that year*/
  
  // Merge Bosnia and Yugoslavia, since it does not exists all years 
  replace IFAUkod = 30 if IFAUkod == 29
  
***Now create fill in the information on country of birth (labels)
  lab def IFAUkod 0 "Sweden"
  lab def IFAUkod 26 "Finland", add
  lab def IFAUkod 27 "Denmark", add
  lab def IFAUkod 28 "Norway & Iceland", add
  lab def IFAUkod 29 "Bosnia", add
  lab def IFAUkod 30 "Yugoslavia", add
  lab def IFAUkod 31 "Poland", add
  lab def IFAUkod 32 "UK & Ireland", add
  lab def IFAUkod 33 "Germany", add
  lab def IFAUkod 34 "Mediterranian", add
  lab def IFAUkod 35 "Baltic", add
  lab def IFAUkod 36 "East Europe", add
  lab def IFAUkod 37 "Hungary & Czech & Slovakia", add
  lab def IFAUkod 38 "Benelux & Swizz & Austria", add
  lab def IFAUkod 39 "US & Canada", add
  lab def IFAUkod 40 "Central America", add
  lab def IFAUkod 41 "Chile", add
  lab def IFAUkod 42 "South America", add
  lab def IFAUkod 43 "East Africa", add
  lab def IFAUkod 44 "Mena", add
  lab def IFAUkod 45 "Other Africa", add
  lab def IFAUkod 46 "Iran", add
  lab def IFAUkod 47 "Iraq", add  
  lab def IFAUkod 48 "Turkey", add
  lab def IFAUkod 49 "East Asia", add
  lab def IFAUkod 50 "South East Asia", add
  lab def IFAUkod 51 "South Asia", add
  lab def IFAUkod 52 "Oceania", add
  lab def IFAUkod 53 "Unknown or Stateless", add

  lab values IFAUkod IFAUkod	

  tab IFAUkod, sort
  
  merge 1:m LopNr using "RTB\Fodelseuppg", keepusing(Kon UtlSvBakG FodArMan)
  keep if _merge==3
  
  gen Ar = 19`y'
  gen Vuxen = Ar-floor(FodArMan/100) > 17 if FodArMan <. 
  drop Ar
  
///population
  gen pop = 1 

  collapse (count) LopNr (sum) Vuxen, by(Kommun IFAUkod) 
  gen Ar = 19`y'
  
  bysort Kommun: egen Storlek_Kom = total(LopNr)
  bysort Kommun: egen Storlek_KomVuxen = total(Vuxen)
  rename LopNr Antal_FLxKom
  rename Vuxen Antal_FLxKomVuxen
  
  save "E:\ProjData\UtplaceringsData8594\enklav_kommun`y'", replace

}
*********************************************************************************
* NOW MERGE ON FAMILY DATA TO DROP THOSE WITH SIBLINGS OR FAMILY IN THE COUNTRY *
*********************************************************************************

foreach y in 85 86 87 88 89 90 91 92 93 94 {
///Siblingdata

use  "E:\ProjData\UtplaceringsData8594\temp`y'"
gen sample=1
	
	merge 1:1 LopNr using  "E:\ProjData\UtplaceringsData8594\hushåll`y'"
	keep if sample==1
	drop _merge
	
	merge 1:1 LopNr using  "E:\ProjData\UtplaceringsData8594\syskon`y'"
	keep if sample==1
	drop _merge


*drop if family
	drop if anhorig==1
	drop if sibling_im==1
	drop sample

save  "E:\ProjData\UtplaceringsData8594\temp`y'", replace
	
}
  
***************************
*FINALY ADD ENCLAVE DATA *
**************************
foreach y in 85 86 87 88 89 90 91 92 93 94 {

  use  "E:\ProjData\UtplaceringsData8594\temp`y'", clear
  replace IFAUkod = 30 if IFAUkod == 29

  merge m:1 Kommun IFAUkod using  "E:\ProjData\UtplaceringsData8594\enklav_kommun`y'"
  drop if LopNr==.
  drop _merge

  duplicates drop LopNr, force 
  save  "E:\ProjData\UtplaceringsData8594\temp`y'", replace
}

  use "E:\ProjData\UtplaceringsData8594\temp85", clear

foreach y in 86 87 88 89 90 91 92 93 94 {
  append using "E:\ProjData\UtplaceringsData8594\temp`y'"
}
  
  keep LopNr Datum im_year Kommun Forsamling Civil IFAUkod FlyktInv DodDatum UtlSvBakG sex Antal_FLxKom* Storlek_Kom* FodArMan
  
  rename im_year InvAr
  rename Datum InvDatum
  rename sex Kon
  rename Kommun InKommun
  rename Forsamling InForsamling
  
  destring FodArMan, replace 
  
// Drop individuals that immigrated more than once during the years 1985-1994
  bysort LopNr (InvAr): egen cID=count(LopNr)
  drop if cID>1
  
// Drop immigrating Swedes
  drop if IFAUkod == 0  
  drop cID
  
  save  "E:\ProjData\UtplaceringsData8594\utp-Inv_8594", replace

